** $datapath : directory with raw data
** $mypath2  : intermediate tables

** As several datasets used in the analysis are proprietary and cannot be shared, 
** this code is only given to help reserachers to understand the set up of our 
** databases and main variables.


********************************************************************************
* This file combines raw key datasets and builds the dataset used for the paper 
* Activity Shocks and Corporate Liquidity: the Role of Trade Credit.
*
* Script: do/Make_base.do

* Authors:	
*			Anne Duquerroy  [anne.duquerroy@banque-france.fr]
*			Benjamin Bureau [benjamin.bureau@banque-france.fr]
*			Frédéric Vinas  [frederic.vinas@banque-france.fr]
* Date: 	Jan. 19, 2024
********************************************************************************

***************************************************************************
** -- This program :
** -- Prepare Fiben dataset  
** -- Prepare Credit Register dataset
** -- Prepare Payment default events dataset
** -- Merge the datasets
***************************************************************************
		
		* input:
		*   ../$datapath/fiben_2016_2020
		*   ../$datapath/SCR
		*   ../$datapath/montant_incident_2019_2020.dta
		*   ../$datapath/credit_et_douteux.dta
		*   ../$datapath/credit_et_douteux
		
		* output:
		* ../$mypath2/fiben_2018_SCR_IP
		* ../$mypath2/douteux
		
		
	
*--------------------------------------------------------------------------*
*  								FIBEN     
*--------------------------------------------------------------------------*
		
		*****************  1. CLEAN FIBEN  ***************	
	
		*************************************
		use $datapath\fiben_2016_2020, clear
		*************************************

		*******  Fiscal year definition ***************	

		lab var annee  "Calendar year"
		gen fmonth  = month(D4)
		gen fyear  = annee if fmonth >= 7
		replace fyear = annee -1 if fmonth < 7
		label var fyear "Fiscal year"
		
		gen cloture_post_lockdown =(fmonth > 2 & annee == 2020 & fyear == 2019)
		bys siren : egen max_c = max(cloture_p)
		
		** -- Keep firms with fiscal year ending before the lockdown (feb 2020)
		gen in_sample = 0			
		replace in_sample = 1 if (fyear == 2019| fyear==2018 | fyear ==2017) & cloture_p == 0
		replace in_sample = 1 if (fyear == 2016) & max_c == 1			
		keep if in_sample == 1

		** -- Drop if no 12 month fiscal year length
		drop if D5!=12
		sort siren fyear

		** Firm size
			gen 	taille_lme_string ="..."			
			replace taille_lme_string ="mic" 	if inlist(taille_lme,1,8)
			replace taille_lme_string ="pme" 	if inlist(taille_lme,2,3,4)
			replace taille_lme_string ="eti" 	if inlist(taille_lme,5)
			replace taille_lme_string ="ge_" 	if inlist(taille_lme,6)			
			replace taille_lme_string ="sci" 	if taille_lme==7
		
		*******  Filters ***************	
		
		** Drop false firms (real estate companies created for fiscal purposes)
		drop if taille_lme==7
		
		** Exclude Financials , Public Administrations and Extra Territorial Activities + Missing Industry
		keep if naf<64 | naf > 66
		drop if naf==0
		drop if naf==.	
		drop if naf==84 | naf==97  | naf==98  | naf==99 
				
	********************************************
	save $mypath2\fiben_sans_doublon, replace
	********************************************
	

	*****************  2. VARIABLES DEFINITION  ***************	
	
		** Drop observations with missing total assets or missing sales			
		drop if ee		== .
		drop if fl		== .
		
		foreach v in  fy fz _5S _5T yu gr hp hq ze zf xq dl aa cm dr yq yr  _1A ac _5U ds dt dm du eh ys yq yr dv dn vi cf cg cd ce {
		replace `v'=0 if `v'==.
		}
		
		foreach v in bv bx cf cd cd cg dw dx fl fs fu fw ys yz yy {
				replace `v'=0 if `v'==.
		}
			
		
		** Financing 
		
		* - Bond finance
		gen oblig 	= ds + dt + dm
		* - Bank loans
		gen empbanc = du - eh 
		* - Current account
		gen cbc 	= eh + ys
		* - Leasing
		gen engcb 	= (coef_a*yq) + (coef_b*yr)  /* The coefficients coef_a and coef_b can not be disclosed (Banque de France's financial analysis methodology)*/
		* - Bank debt
		gen banc 	= empbanc + cbc + engcb
		* - Other loans
		gen autemp2 = dv + dn + vi - VI2
		* - Total financial debt
		gen endfi2 	= oblig + banc + autemp2 + VI2
		
		** Total Assets
		tsset siren fyear
		gen ta_lag = L1.ee
		
		** Leverage 
		gen leverage = endfi2 / ta_lag
		
		** Staff costs
		gen chpers  = fy + fz + _5S - _5T + yu 
		
		** Interest expenses
		gen preteurr = (gr + coef_c*hp + coef_d*hq) /* The coefficients coef_c and coef_d can not be disclosed (Banque de France's financial analysis methodology)*/
		
		** Dividend paid
		gen div_pay = ze + zf
		
		** Cash and short-term liquid assets			
		gen cash    =(cf+cd)
		gen cash_ta =(cf+cd)/ta_lag
		
		** Rents
		rename xq 	  charges_loc
		
		** Ratios of receivables and payables 
		
		* - Sales 
		gen cattc 		= fl+yy							
		gen ca_lag		= L1.cattc 
		* - Purchases
		gen achatsttc 	= fs+fu+fw+yz
		* - Payables in nber of days of purchases
		gen  delai_frnissr 	  = 360 * (dx-bv) / achatsttc 
		gen  dfac = (dx-bv) / achatsttc 
		lab var dfac "Payables over Purchases"
		
		* - Receivables in nber of days of sales
		gen  delai_clnt 	  = 360 * (bx+ys-dw) / cattc 	
		gen  ccca 	  = (bx+ys-dw) / cattc 
		lab var ccca  "Receivable sover Sales"
		
		* - Trade credit over sales
		gen cie_ca		=	(bx+ys-dw-dx+bv)  / cattc
		lab var cie_ca 		"Trade Credit over Sales"
		
		
		** Winsorization of ratios at 1%
		foreach v in cash_ta div_ta leverage cash_ta cie_ca delai_frnissr delai_clnt ccca dfac  {
			replace `v'=0 if `v'==.
			winsor `v' , gen(`v'w) p(0.01)
			drop `v'
			rename `v'w `v'
				}

		
	
	***************** 3. Identify sectors in retail trade that were forced to shut down or not
	
	
			/* Le décret du 23 mars 2020 définit les activités autorisées pendant le confinement.

			"Les activités mentionnées au II de l'article 8 sont les suivantes :
			Entretien et réparation de véhicules automobiles, de véhicules, engins et matériels agricoles.
			Commerce d'équipements automobiles.
			Commerce et réparation de motocycles et cycles.
			Fourniture nécessaire aux exploitations agricoles.
			4711A Commerce de détail de produits surgelés.
			4711B Commerce d'alimentation générale.
			4711C Supérettes.
			4711D Supermarchés.
			4711E Magasins multi-commerces.
			4711F Hypermarchés.
			4721 Commerce de détail de fruits et légumes en magasin spécialisé.
			4722 Commerce de détail de viandes et de produits à base de viande en magasin spécialisé.
			4723 Commerce de détail de poissons, crustacés et mollusques en magasin spécialisé.
			4724 Commerce de détail de pain, pâtisserie et confiserie en magasin spécialisé.
			4725 Commerce de détail de boissons en magasin spécialisé.
			4729 Autres commerces de détail alimentaires en magasin spécialisé.
			Les distributions alimentaires assurées par des associations caritatives.
			4730 Commerce de détail de carburants en magasin spécialisé.
			4740 Commerce de détail d'équipements de l'information et de la communication en magasin spécialisé.
				4741 Commerce de détail d'ordinateurs, d'unités périphériques et de logiciels en magasin spécialisé.
				4742 Commerce de détail de matériels de télécommunication en magasin spécialisé.
			4752 (pas exactement le même texte) Commerce de détail de matériaux de construction, quincaillerie, peintures et verres en magasin spécialisé.
			4762 Commerce de détail de journaux et papeterie en magasin spécialisé.
			4773 Commerce de détail de produits pharmaceutiques en magasin spécialisé.
			4774 Commerce de détail d'articles médicaux et orthopédiques en magasin spécialisé.
			4778A Commerces de détail d'optique."
			*/	
			
			gen com_détail=0
			replace com_détail=1 if naf4digit>=4700 & naf4digit<=4799 
			lab var com_détail "Retail trade sector"
			
			gen com_détail_autorisé=0
			replace com_détail_autorisé=1 if inlist(naf4digit,4711) 
			replace com_détail_autorisé=1 if inlist(naf4digit,4721,4722,4723,4724,4725,4729)
			replace com_détail_autorisé=1 if inlist(naf4digit,4730)
			replace com_détail_autorisé=1 if naf4digit>=4740 & naf4digit<4750 
			replace com_détail_autorisé=0 if naf4digit==4752
			replace com_détail_autorisé=1 if naf4digit==4762
			replace com_détail_autorisé=1 if naf4digit==4773
			replace com_détail_autorisé=1 if naf4digit==4774
			replace com_détail_autorisé=1 if naf5digit=="4778A"
			lab var com_détail_autorisé "Retail trade actvities allowed to open during lockdown"
	
		
			***********************************
			save $mypath2\fiben_clean, replace
			***********************************
	
*--------------------------------------------------------------------------*
*  						CREDIT REGISTER SCR 2017 - 2021
*--------------------------------------------------------------------------*

				
			***********************************
			use $datapath\SCR, replace
			***********************************
				
				rename month fmonth
	
				** -- Agregate at siren x bank level x fiscal year end
				collapse (sum) mob* ct mlt cc af ca (last) com* depart* pays cat_jur cotation indic ,by(siren banque fmonth annee)				
	
				** -- Agregate credit at firm x fiscal year end level
				collapse (sum) mob* ct mlt cc af ca  (last) com* depart* pays cat_jur cotation indic ,by(siren fmonth annee)
				
				
				merge 1:1 siren fmonth annee using $mypath2\fiben_temp
				drop if _m == 1
				drop _m
				
			********************************	
			save $mypath2\fiben_scr, replace
			********************************
													
			
*--------------------------------------------------------------------------*
*  							CIPE - PAYMENT DEFAULT DATA
*--------------------------------------------------------------------------*
			
			** Data provided is already aggregated at Firm x month information
			
			/* The main dependent variable is defined as : 
				gen incident 			= (inlist(motif,"75", "31", "20" , "32", "34", "35", "70", "73", "71", "74", "76", "90" ))
				gen firm_with_ip    	= (incident > 0 & incident != .)
				lab var firm_with_ip "Payment Default dummy"
			*/
			
			****************************************************************
			use $datapath\montant_incident_2019_2020.dta, replace
			****************************************************************	
				rename year_rej 	year
				rename month_rej 	month
				
				gen date = ym(year, month)
				format date %tm
				
				rename siren_num siren
				lab var siren "Firm ID"
				
				* Amount in K euros
				foreach v in "" "20" "31" "32" "34" "35" "70" "71" "73" "74" "75" "76" "90"   {			 
							gen ip`v' = (montant_mensuel_ip`v'/1000)				
				}
				
				replace incident=0 if incident==.
				rename  incident ipe
				
				gen ipe_illiquidity = (inlist(motif,"75", "31", "20"))
				gen ipe_dispute 	= (inlist(motif,"70", "73", "74", "76", "90"))
		
				lab var ipe_illiquidity     "Liquidity default"
				lab var ipe_dispute   		"Dispute default"
				 
				
				******************************	
				save $mypath2\ip.dta, replace
				******************************	
	
				******************************		
				use $mypath2\fiben_scr, replace
				******************************
			
				** Generate 12 months for each year
					expand (12)
					bys siren fyear: gen month=_n				
					
					gen year_ip = fyear + 1
					sort siren year_ip month			
					gen date = ym(year_ip, month)
					format date %tm
					
					xtset siren date, monthly						
					
					merge 1:1 siren date using "$mypath\ip.dta"
					drop if _merge	== 2
					drop _merge
					
				** Ratios of Amount under default over Sales, over Payables
					foreach  z in "cattc" {		
						foreach v in  "" "_incapacite" "_contest"    {			 
							gen montant`v'_`z' = (ip`v')/`z'
						}
					}
				
						foreach v in  "" "_incapacite" "_contest"    {			 
							gen montant`v'_dlfr= (ip`v')/(dx-bv)
						}
				
					label variable montant_dlfr		"Monthly AuD / Payables"
					label variable montant_ca		"Monthly AuD / Sales"
					
					** Winsorize ratios at 1%
					
					foreach v in  "" "_incapacite" "_contest"  {			 
						winsor montant`v'_dlfr, gen(montant`v'_dlfrw) p(0.01)
						drop montant`v'_dlfr
						rename montant`v'_dlfrw montant`v'_dlfr
						}
					
					foreach  z in "cattc" {	
					foreach v in  "" "_incapacite" "_contest"  {			 
						winsor montant`v'_`z', gen(montant`v'_`z'w) p(0.01)
						drop montant`v'_`z'
						rename montant`v'_`z'w montant`v'_`z'
						}
					}
					
		
				** CREDIT RATING : create numeric value
				tab cotation
				gen rating = substr(cotation,2,3)
		
				* Recode rating numerically from 0 to 12
				gen rating_num=0 if rating=="0"
				replace rating_num = 1 if rating == "3++"
				replace rating_num = 2 if rating == "3+"
				replace rating_num = 3 if rating == "3"
				replace rating_num = 4 if rating == "4+"
				replace rating_num = 5 if rating == "4"
				replace rating_num = 6 if rating == "5+"
				replace rating_num = 7 if rating == "5"
				replace rating_num = 8 if rating == "6"
				replace rating_num = 9 if rating == "7"
				replace rating_num = 10 if rating == "8"
				replace rating_num = 11 if rating == "9"
				replace rating_num = 12 if rating == "P"
				
				tsset siren date
				gen rating_lag  = L1.rating_num
				gen rating_lag2 = L2.rating_num
				gen rating_lag3 = L3.rating_num
				
				distinct siren
				* 302 142
				
				**********************************************			
				save $mypath2/fiben_2018_SCR_IP, replace
				**********************************************
				
				
		*----------------------------------------------------------------------------------------------------------*
		*----------------------------------------------------------------------------------------------------------*
		
		** -- Data provided at month x firm level 
		** -- Prepare monthly information on doubtful loans and short-term loans 
		
		**********************************************
		use $datapath2\credit_et_douteux.dta, replace
		**********************************************
		
		** -- Calculate quaterly variable of interest
				
				** -- Non performing loans 
				foreach var in bd cd hd td {
				replace `var' = 0 if `var' ==.
				}

				gen douteux = bd + cd + td
				gen date = ym(annee,mois)
				format date %tm
				tsset siren date
				gen     ratio_douteux = douteux / mobilises
				replace ratio_douteux = 0 if ratio_douteux == .
				replace ratio_douteux = 1 if ratio_douteux > 1
				
				lab var ratio_douteux "Non performing loans over total drawn credit"
				
				gen ratio_douteux_lag  =L1.ratio_douteux
				gen ratio_douteux_lag2 =L2.ratio_douteux
				gen ratio_douteux_lag3 =L3.ratio_douteux
				
				** -- Share of short-term loans
				gen share_st = ct/mobilises
				replace share_st = 0 if share_st == .
				
				lab var share_st "Short-term loans over total drawn credit" 
				
				** Lagged
				gen share_st_lag  =L1.share_st
				gen share_st_lag2 =L2.share_st
				gen share_st_lag3 =L3.share_st
		
				drop if annee < 2019
		
				keep siren date ratio_douteux* share_st*
		
				********************************************
				save $mypath2\douteux.dta, replace
				********************************************
			
			
		*----------------------------------------------------------------------------------------------------------*
		*----------------------------------------------------------------------------------------------------------*
		
		** -- Prepare data on Altman Z score
		** -- Cf. Altamn_score_SAS_code
	
			
			
				
				
